With the advent of Web application servers, the use of large objects (LOBs) in database systems is increasing. In many cases, these LOBs are used to store session state: serialized Java objects or other structures known to applications accessing the database systems. Once requested, retrieval of the LOBs from database servers to the application in a performance efficient fashion is critical.
Typically, these LOB objects are relatively small (i.e., less than 10 K), but on occasion they can be quite large (i.e., greater than 100 K). To take into account future growth, database administrators typically define quite large sizes (i.e., greater than 1 GB) for LOB columns of the database. If the database administrator knew these objects would never have the possibility of becoming LOBs, the column could be defined as long varchar for bit data. It is assumed that defining columns as LOBs indicates the corresponding objects will occasionally have very large values (i.e. considered as LOBs). There currently exists two methods for retrieving LOBs from the database, with current architectures: by defining a locator, or by asking for the LOB value.
The locator approach can have an advantage that only a handle flow is returned from the database server to the application. The actual LOB value remains on the database server until the application is ready to fetch the LOB value or any part of the LOB value. A disadvantage to the locator approach, especially in the case where the LOBs are relatively small, can be that an additional network flow to the database server is required to retrieve each LOB. Therefore, by always specifying locators in response to the data request, system drivers are forcing a second trip to the database server to retrieve the value of every LOB, thus exacting a potential system performance penalty.
An alternative approach for LOB retrieval is that of fetching the LOB value up front. LOB value retrieval can be more appropriate for the case of small LOBs, but can consume a considerable amount of memory on the client for large LOBs, especially if the application was only interested in a small portion of the large LOB. One disadvantage of the LOB retrieval method is that by always specifying the value to be returned, the client of the application can be occasionally hit by a very large LOB, which can force a significant amount of memory to be used at the client for LOB buffering. Further, the application may not require the entire LOB, and as a result may cause inefficiencies in client memory allocation and utilization.
Consequently, with current architectures, the application (or for example a JDBC/ODBC driver) must make the decision up front to retrieve the LOB either by the locator or by value. This decision is typically made by the driver without assistance from the application; the driver usually selects the locator approach. The locator approach can be the recommended approach for JDBC implementers to use. In either case, the decision for retrieval type is made at the client with possible knowledge of a defined maximum length of the column (which is often quite large), but without knowledge of the actual length of the particular resident LOB value in the corresponding database field.
What is therefore needed is a system and associated method that enable the database management system to make a dynamic decision for sending either the LOB locator or the LOB value in the fetch response, depending upon the actual value of the LOB in comparison to the threshold value. The need for such system and method has heretofore remained unsatisfied.